# 对2中的表结构，用SQLAchemy模块来实现同样的操作

from sqlalchemy import Column, String, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

engine = create_engine('mysql+mysqlconnector://root:w406833650@localhost:3306/test')

DBSession = sessionmaker(bind = engine)

class lyb(Base):
    __tablename__ = 'test'

    ID = Column(String(255))
    message = Column(String(255))
    user = Column(String(255))
    time = Column(String(255))
    delete = Column(String(20))

def add():
    id = input("ID:")
    message = input("message:")
    use = input("user:")
    time = input("time:")
    dele = input("delete:")
    new_lyb = lyb(ID = id, message = message, user = use, time = time, delete = dele)
    try:
        session = DBSession()
        session.add(new_lyb)
        session.commit()
    except:
        print("Error")

def change():
    try:
        session = DBSession()
        session.query(lyb).filter(lyb.time == '2020-06-05').update({"time": "2020-06-06"})
        session.commit()
    except:
        print("Error")

def search():
    c = input("The ID you want to search:")
    try:
        session = DBSession()
        search_lyb = session.query(lyb).filter(lyb.ID == c).one()
        print('message:',search_lyb.message,'; user:', search_lyb.user, '; time:', search_lyb.time, '; delete:', search_lyb.delete)
    except:
        print("Error")

def delete():
    try:
        session = DBSession()
        session.query(lyb).filter(lyb.ID == '1001').delete()
        session.commit()
    except:
        print("Error")


while True:
    print("Please input your operation:")
    operation = input()

    if operation == "add":
        add()
    elif operation == "search":
        search()
    elif operation == "change":
        change()
    elif operation == "delete":
        delete()
    elif operation == "over":
        break

db.close()